﻿Imports System.Data.SqlClient
Public Class VendaDAL


    Public Function RetItens(Codigo As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "select *from vw_venItensvenda where ven_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
            Return tab
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function RetItem(Codigo As Integer, ProCodigo As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "select *from vw_venItensvenda where ven_codigo = @codigo and pro_codigo=@procodigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@procodigo", SqlDbType.Int)).Value = ProCodigo
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
            Return tab
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function IAEVenda(Codigo As Integer, Data As Nullable(Of DateTime), CodFuncionario As Integer, CodCliente As Integer, CodStatus As Integer, Operacao As Char, Obs As String) As Integer
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "execute SPVenda @data,@codfuncionario,@codcliente,@codstatus,@vencodigo,@operacao,@obs "
            If (Data.HasValue) Then : cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.DateTime)).Value = Data
            Else : cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.DateTime)).Value = DBNull.Value
            End If

            cmd.Parameters.Add(New SqlParameter("@codfuncionario", SqlDbType.Int)).Value = CodFuncionario
            cmd.Parameters.Add(New SqlParameter("@codcliente", SqlDbType.Int)).Value = CodCliente
            cmd.Parameters.Add(New SqlParameter("@codstatus", SqlDbType.Int)).Value = CodStatus
            cmd.Parameters.Add(New SqlParameter("@vencodigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@operacao", SqlDbType.Char)).Value = Operacao
            cmd.Parameters.Add(New SqlParameter("@obs", SqlDbType.VarChar)).Value = Obs
            Codigo = objconexao.executacmdAI(cmd, "Ofi_OrdemVenda")
            Return Codigo
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function InsereOrdemServico(Codigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "exec SPOrdemVenda @codigo "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            Codigo = objconexao.executacmdAI(cmd, "Ofi_OrdemVenda")
            Return Codigo
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub IAEItens(VenCodigo As Integer, ProCodigo As Integer, ItoQtde As Integer, ItoValorUnitario As Decimal, ItoDesconto As Decimal, Total As Decimal, StaCodigo As Integer, Operacao As Char)
        Dim cmd = New SqlCommand
        Try
            cmd.CommandText = "execute SPVendaEstoque @vencodigo,@procodigo, @itoqtde,@itovalorunitario,@itodesconto,@total,@stacodigo,@operacao "
            cmd.Parameters.Add(New SqlParameter("@vencodigo", SqlDbType.Int)).Value = VenCodigo
            cmd.Parameters.Add(New SqlParameter("@procodigo", SqlDbType.Int)).Value = ProCodigo
            cmd.Parameters.Add(New SqlParameter("@itoqtde", SqlDbType.Int)).Value = ItoQtde
            cmd.Parameters.Add(New SqlParameter("@itovalorunitario", SqlDbType.Decimal)).Value = ItoValorUnitario
            cmd.Parameters.Add(New SqlParameter("@itodesconto", SqlDbType.Decimal)).Value = ItoDesconto
            cmd.Parameters.Add(New SqlParameter("@total", SqlDbType.Decimal)).Value = Total
            cmd.Parameters.Add(New SqlParameter("@stacodigo", SqlDbType.Int)).Value = StaCodigo
            cmd.Parameters.Add(New SqlParameter("@operacao", SqlDbType.Char)).Value = Operacao
            objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_VenVenda where ven_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_VenVenda where cli_nome COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 3
                    If (Descricao = "Aberto") Then
                        cmd.CommandText = "select * from vw_VenVenda where sta_codigo<>5"
                    Else
                        cmd.CommandText = "select * from vw_VenVenda where sta_codigo=5"
                    End If
                Case 8
                    cmd.CommandText = "select * from vw_VenCargo order by car_codigo"
                Case 9
                    cmd.CommandText = "select * from vw_VenFuncionario order by car_codigo"
                Case Else
                    cmd.CommandText = "select * from vw_VenVenda  order by ven_codigo"
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function

    Function locadata(ByVal dtini As Date, ByVal dtfim As Date) As DataTable 'Filtro datas
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand  'Format(Descricao, "yyyy/MM/dd")
            cmd.CommandText = "select * from vw_VenVenda where ven_data BETWEEN @dtini AND @dtfim"
            cmd.Parameters.Add(New SqlParameter("@dtini", SqlDbType.DateTime)).Value = Format(dtini, "yyyy/MM/dd 00:00:00")
            cmd.Parameters.Add(New SqlParameter("@dtfim", SqlDbType.DateTime)).Value = Format(dtfim, "yyyy/MM/dd 23:59:59")
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
End Class
